﻿using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Text;

namespace HZY.DbExtensions
{
    using HzySql;
    using HzySql.Core.SqlServer;
    using HzySql.Curd.Select;
    using HzySql.Dapper.SqlServer;
    using System.Data;
    using System.Linq;

    /// <summary>
    /// HzySql 数据操作对象
    /// </summary>
    public class HzySqlDbContext : HzySqlSqlServerDapper
    {
        public HzySqlDbContext(string dbConnectionString)
            : base(dbConnectionString) { }

        public HzySqlDbContext(string dbConnectionString, PagingMode pagingMode)
            : base(dbConnectionString, pagingMode) { }

        public async Task<TableViewModel> AsTableViewModelAsync(IQueryData queryData, int Page, int Rows, int Count, Action<TableViewModel> Call = null)
        {
            var _tableViewModel = new TableViewModel();

            _tableViewModel.Page = Page;
            _tableViewModel.Rows = Rows;
            _tableViewModel.TotalCount = Count;
            _tableViewModel.TotalPage = (_tableViewModel.TotalCount / Rows);

            var Datas = await queryData.ToTableAsync();

            var sqlCodeContext = queryData.ToSqlContext();
            var tableNames = sqlCodeContext.GetTableNames();

            foreach (DataColumn dc in Datas.Columns)
            {
                var col = new TableViewCol();
                col.DataIndex = dc.ColumnName;
                col.Show = dc.ColumnName != "_ukid";

                var fieldInfo = ModelSet.GetFieldInfos(w => tableNames.Contains(w.TableName) && w.ColumnName == dc.ColumnName).FirstOrDefault();
                if (fieldInfo != null)
                {
                    col.Title = string.IsNullOrWhiteSpace(fieldInfo.Remarks) ? col.DataIndex : fieldInfo.Remarks;
                }
                else
                {
                    col.Title = dc.ColumnName;
                }

                _tableViewModel.Cols.Add(col);
            }

            _tableViewModel.DataSource = Datas.ToList();

            Call?.Invoke(_tableViewModel);

            return _tableViewModel;
        }

        /// <summary>
        /// 根据表名称 获取列
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public async Task<List<TABLES_COLUMNS>> GetColsByTableNameAsync(string TableName)
        {
            // SELECT 
            //     表名       = case when a.colorder=1 then d.name else '' end,
            //     表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
            //     字段序号   = a.colorder,
            //     字段名     = a.name,
            //     标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
            //     主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
            //                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
            //     类型       = b.name,
            //     占用字节数 = a.length,
            //     长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
            //     小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
            //     允许空     = case when a.isnullable=1 then '√'else '' end,
            //     默认值     = isnull(e.text,''),
            //     字段说明   = isnull(g.[value],'')
            // FROM 
            //     syscolumns a
            // left join 
            //     systypes b 
            // on 
            //     a.xusertype=b.xusertype
            // inner join 
            //     sysobjects d 
            // on 
            //     a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
            // left join 
            //     syscomments e 
            // on 
            //     a.cdefault=e.id
            // left join 
            // sys.extended_properties   g 
            // on 
            //     a.id=G.major_id and a.colid=g.minor_id  
            // left join
            // sys.extended_properties f
            // on 
            //     d.id=f.major_id and f.minor_id=0
            // where  
            //     d.name='member' 
            // order by  
            //     a.id,a.colorder

            string SqlString = $@"
SELECT 
    d.name AS TabName,
	isnull(f.value,'') AS TabNameRemark,
	a.colorder AS ColOrder,
	a.name AS ColName,
	case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 1 else 0 end ColIsIdentity,
	case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end ColIsKey,
	b.name AS ColType,
	a.length AS ColLength,
	COLUMNPROPERTY(a.id,a.name,'PRECISION') AS ColMaxLength,
	isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) AS ColScale,
	case when a.isnullable=1 then 1 else 0 end ColIsNull,
	isnull(e.text,'') AS ColDefaultValue,
    isnull(g.[value],'') AS ColRemark
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=G.major_id and a.colid=g.minor_id  
left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name=@TableName
order by a.id,a.colorder
";
            var dataParameter = new List<HzySql.Models.DataParameter>();
            dataParameter.Add(new HzySql.Models.DataParameter("TableName", TableName));
            var list = await this.AdoProvider.QueryAsync<TABLES_COLUMNS>(SqlString, dataParameter);
            return list.ToList();
        }

        /// <summary>
        /// 获取所有的表名称
        /// </summary>
        /// <returns></returns>
        public async Task<List<string>> GetAllTableAsync()
        {
            string SqlString = @"SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES order by TABLE_NAME";

            return (await this.AdoProvider.QueryAsync<string>(SqlString, null)).ToList();
        }

    }
}
